package com.dhcc.wb.articleday;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.dhcc.common.database.DBManager;
import com.dhcc.common.util.StringUtil;
import com.dhcc.wb.articleliushui.ArticleGzhModel;
import com.dhcc.wb.articleliushui.ArticleShowModel;
import com.dhcc.wb.gongzhonghao.GzhModel;
import com.dhcc.wb.gongzhonghao.UtilArticleModel;

public class ArticledayDao {
	private static final Logger logger = Logger.getLogger(ArticledayDao.class);
	
	
	//新增
	public int addArticle(String ids, String ref_date,String msgid,String title,Integer int_page_read_user,
			Integer int_page_read_count,Integer ori_page_read_user,Integer ori_page_read_count,
			Integer share_user,Integer share_count,Integer add_to_fav_user,Integer add_to_fav_count,String gzhid,String gzhname) {
		int i=0;
		DBManager dbm = null;
		try{
			dbm = new DBManager();
			String sql = "insert into weixin_article_day(ids,ref_date,msgid,title,int_page_read_user,int_page_read_count,ori_page_read_user," +
					"ori_page_read_count,share_user,share_count,add_to_fav_user,add_to_fav_count,gzhid,gzhname) values('"+ ids +"','"+ ref_date +"','"+ msgid +"','"+title+"','"+int_page_read_user+
					"','"+int_page_read_count+"','"+ori_page_read_user+"','"+ori_page_read_count+"','"+share_user+"','"+share_count+"','"+add_to_fav_user+"','"+add_to_fav_count+"','"+gzhid+"','"+gzhname+"')";
			i = dbm.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			dbm.close();
		}
		return i;
	}
	
	
	//验证唯一
	public ArticleModel queryArticle_result(String title,String msgid,String ref_date,String gzhid) {
		ArticleModel model = null;
		DBManager dbm = null;
		try{
			dbm = new DBManager();
			String sql = "select * from weixin_article_day "
					+ " where title = '"+ title +"' and msgid='"+msgid+"' and ref_date='"+ref_date+"' and gzhid ='"+gzhid+"'";
			model = (ArticleModel) dbm.getObject(ArticleModel.class, sql);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			dbm.close();
		}
		return model;
	}
	
	
	
	/*
	 * 查询
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleModel> findArticleDayServiceByPage(Integer pageNumber,Integer pageSize,String title,String gzhname) {
		DBManager dbm = null;
		List<ArticleModel> list = null;
		try {
			dbm = new DBManager();
			String sql = "select * from weixin_article_day where 1=1 ";
		    if(!StringUtil.isNullOrEmpty(title)){
			   sql += " and title like  '%"+ title +"%'";			   
		    }
		    if(!StringUtil.isNullOrEmpty(gzhname)){
				   sql += " and gzhname like  '%"+ gzhname +"%'";			   
			    }
		    sql += " order by ref_date desc";
	        sql +=  " limit " + pageNumber + ","+ pageSize +"";
			list = dbm.getObjectList(ArticleModel.class, sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	public Integer queryCount(String title) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			dbm = new DBManager();
			String sql = "select count(*) from weixin_article_day  where 1=1 ";
		    if(!StringUtil.isNullOrEmpty(title)){
				   sql += " and title like  '%"+ title +"%'";			   
			   }
			count = dbm.executeQueryCount(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	
	
	/*
	 * 文章列表查询
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleShowModel> findArticleServiceByPage(Integer pageNumber,Integer pageSize,String title) {
		DBManager dbm = null;
		List<ArticleShowModel> list = null;
		try {
			dbm = new DBManager();
			String sql = "select t.title,SUM(t.int_page_read_count) as readcount from weixin_article_day t where 1=1 ";
		    if(!StringUtil.isNullOrEmpty(title)){
			   sql += " and title like  '%"+ title +"%'";
		    }
		    sql += " GROUP BY t.title";
	        sql +=  " limit " + pageNumber + ","+ pageSize +"";
			list = dbm.getObjectList(ArticleShowModel.class, sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	
	
	/*
	 * 文章管理 二级页面 属于哪些公众号
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleGzhModel> findArticleDayService(String title) {
		DBManager dbm = null;
		List<ArticleGzhModel> list = null;
		try {
			dbm = new DBManager();
			String sql = "select  t.title,t.gzhname,SUM(t.int_page_read_count) AS counts " +
					" from  weixin_article_day t where 1=1 and " +
					" t.title='"+title+"' group by t.gzhname ";
			list = dbm.getObjectList(ArticleGzhModel.class, sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	/*
	 * 文章管理 三级页面 某个公众号 某篇文章
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleGzhModel> findArticleDayService(String title,String gzhname) {
		DBManager dbm = null;
		List<ArticleGzhModel> list = null;
		try {
			dbm = new DBManager();
			String sql = "select  t.title,t.gzhname,t.int_page_read_count AS counts,t.ref_date " +
					" from  weixin_article_day t where 1=1 and " +
					" t.title='"+title+"' and t.gzhname='"+gzhname+"' order by t.ref_date desc";
			list = dbm.getObjectList(ArticleGzhModel.class, sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	//昨天文章点击量
	public Integer queryCount2(String ref_date,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			if(!StringUtil.isNullOrEmpty(ref_date)){
				dbm = new DBManager();
//				String sql = "select SUM(t.int_page_read_count) from weixin_article_day t where t.ref_date = '"+ref_date+"'";
				StringBuffer sb = new StringBuffer("select SUM(t.int_page_read_count) from weixin_article_day t where t.ref_date = '"+ref_date+"'");
				if(gzhlist != null && gzhlist.size()>0){
					sb.append( " and gzhid in (");
					for(GzhModel gzh : gzhlist){
						sb.append("'"+gzh.getGzhid()+"',");
					}
					sb.deleteCharAt(sb.length()-1);
					sb.append(")");
					count = dbm.executeQueryCount(sb.toString());
				}else{
					count = 0;
				}
			   }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	
	
	
	
	//昨天文章数量
	public Integer queryCount3(String ref_date,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			if(!StringUtil.isNullOrEmpty(ref_date)){
				dbm = new DBManager();
				
//				String sql = "select count(distinct t.title) from weixin_article_day t where t.ref_date = '"+ref_date+"'";
				
				StringBuffer sb = new StringBuffer("select count(distinct t.title) from weixin_article_day t where t.ref_date = '"+ref_date+"'");
				if(gzhlist != null && gzhlist.size()>0){
					sb.append( " and gzhid in (");
					for(GzhModel gzh : gzhlist){
						sb.append("'"+gzh.getGzhid()+"',");
					}
					sb.deleteCharAt(sb.length()-1);
					sb.append(")");
					count = dbm.executeQueryCount(sb.toString());
				}else{
					count = 0;
				}
				
			   }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	
	//访问用户
	public Integer queryCount4(String ref_date,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			if(!StringUtil.isNullOrEmpty(ref_date)){
				dbm = new DBManager();
//				String sql = "select sum(t.int_page_read_user) from weixin_article_day t where t.ref_date = '"+ref_date+"'";
				
				StringBuffer sb = new StringBuffer("select sum(t.int_page_read_user) from weixin_article_day t where t.ref_date = '"+ref_date+"'");
				if(gzhlist != null && gzhlist.size()>0){
					sb.append( " and gzhid in (");
					for(GzhModel gzh : gzhlist){
						sb.append("'"+gzh.getGzhid()+"',");
					}
					sb.deleteCharAt(sb.length()-1);
					sb.append(")");
					count = dbm.executeQueryCount(sb.toString());
				}else{
					count = 0;
				}
			   }
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	
	
	/*
	 * 某天读过的标题
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleTitleModel> findArticleDay(String ref_date,List<GzhModel> gzhlist) {
		DBManager dbm = null;
//		List<ArticleModel> list = new ArrayList<ArticleModel>();
		List<ArticleTitleModel> list = new ArrayList<ArticleTitleModel>();
		try {
			dbm = new DBManager();
//			String sql = "select  * from  weixin_article_day t where t.ref_date='"+ref_date+"' group by t.title";
			
			
			StringBuffer sb = new StringBuffer("select t.title  from  weixin_article_day t where t.ref_date='"+ref_date+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
				sb.append(" group by t.title");
			}else{
				sb.append(" and 1=2");
			}
			
			list = dbm.getObjectList(ArticleTitleModel.class, sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	/*
	 * 某天 读过文章 
	 *阅读人数
	 */
	@SuppressWarnings("unchecked")
	public Integer findArticleDay1(String ref_date,String title,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			dbm = new DBManager();
//			String sql = "select sum(t.int_page_read_user) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'";
			
			StringBuffer sb = new StringBuffer("select sum(t.int_page_read_user) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
				count = dbm.executeQueryCount(sb.toString());
			}else{
				count = 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	/*
	 * 某天 读过文章
	 * 公众号个数
	 */
	public List<ArticleModel> findArticleDay5(String ref_date,String title,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		List<ArticleModel> list = new ArrayList<ArticleModel>();
		try {
			dbm = new DBManager();
			StringBuffer sb = new StringBuffer("select t.gzhname from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
				sb.append("GROUP BY t.gzhname");
			}
			list = dbm.getObjectList(ArticleModel.class, sb.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
	
	
	
	/*
	 * 某天 读过文章 
	 * 公众号个数
	 */
	@SuppressWarnings("unchecked")
	public Integer findArticleDay2(String ref_date,String title,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			dbm = new DBManager();
//			String sql = "select count(*) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'";
			
			
			StringBuffer sb = new StringBuffer("select count(*) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
				count = dbm.executeQueryCount(sb.toString());
			}else{
				count = 0;
			}
			
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	/*
	 * 某天 读过文章 
	 * 阅读次数
	 */
	@SuppressWarnings("unchecked")
	public Integer findArticleDay3(String ref_date,String title,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		Integer count = 0;
		try {
			dbm = new DBManager();
//			String sql = "select sum(t.int_page_read_count) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'";
			
			
			StringBuffer sb = new StringBuffer("select sum(t.int_page_read_count) from weixin_article_day t where t.ref_date='"+ref_date+"'  AND t.title='"+title+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
				count = dbm.executeQueryCount(sb.toString());
			}else{
				count = 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return count;
	}
	
	/**
	 * 文章阅读排行
	 * 入参：时间 文章title
	 * 返回：文章title,每个公众号的阅读人数、阅读数量
	 */
	@SuppressWarnings("unchecked")
	public List<ArticleModel> findArticleDay4(String ref_date,String title,List<GzhModel> gzhlist) {
		DBManager dbm = null;
		List<ArticleModel> list = new ArrayList<ArticleModel>();
		try {
			dbm = new DBManager();
//			String sql = "select  * from  weixin_article_day t where t.ref_date='"+ref_date+"' and  t.title = '"+title+"'";
			
			StringBuffer sb = new StringBuffer("select  * from  weixin_article_day t where t.ref_date='"+ref_date+"' and  t.title = '"+title+"'");
			if(gzhlist != null && gzhlist.size()>0){
				sb.append( " and gzhid in (");
				for(GzhModel gzh : gzhlist){
					sb.append("'"+gzh.getGzhid()+"',");
				}
				sb.deleteCharAt(sb.length()-1);
				sb.append(")");
			}else{
				sb.append(" and 1=2");
			}
			
			if(StringUtil.isNullOrEmpty(title)){
				sb.append(" and 1=2");
			}
//			System.out.println(sb.toString());
			list = dbm.getObjectList(ArticleModel.class, sb.toString());
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbm.close();
		}
		return list;
	}
		
}
